﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DbProvider
/// </summary>
public class DbProvider
{
    SqlConnection sqlConn;
    SqlCommand sqlCmd;
	public DbProvider()
	{
        clsConnection connect = new clsConnection(".", "BanDongHo", true);
        sqlConn = new SqlConnection(connect.getConnectionString());
        sqlCmd = sqlConn.CreateCommand();

	}
    //Phuong thuc mo ket noi
    public void Open()
    {
        if (sqlConn.State != ConnectionState.Open)
            sqlConn.Open();
    }

    //Phuong thuc giai phong doi tuong (tai nguyen)
    public void Dispose()
    {
        //Ngat ket noi
        if (sqlConn.State != ConnectionState.Closed)
            sqlConn.Close();
        //Giai phong doi tuong
        sqlConn.Dispose();
        sqlCmd.Dispose();
    }

    //Phuong thuc them cac tham so vao Command
    public void AddParameter(SqlParameter para)
    {
        sqlCmd.Parameters.Add(para);
    }

    //Phuong thuc rut trich du lieu (SELECT)
    public DataTable ExecuteQuery(string strSQL)
    {
        DataTable tbObject = new DataTable();
        SqlDataAdapter sqlAdap = new SqlDataAdapter();
        try
        {
            //Mo ket noi
            this.Open();
            //Gan cac thuoc tinh cho doi tuong Command
            sqlCmd.CommandText = strSQL;
            sqlCmd.CommandType = CommandType.Text;
            sqlAdap.SelectCommand = sqlCmd;
            //Do du lieu
            sqlAdap.Fill(tbObject);
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        
        return tbObject;
    }

    //Phuong thuc cap nhat du lieu (INSERT, DELETE, UPDATE, ...)
    public int ExecuteNonQuery(string strSQL)
    {
        int row = 0;
        try
        {
            this.Open();
            sqlCmd.CommandText = strSQL;
            sqlCmd.CommandType = CommandType.Text;
            row = sqlCmd.ExecuteNonQuery();//Dung de thuc thi cac cau lenh: Insert, Delete, Update, .....
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        
        return row;
    }
}